{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#导入模块\n",
    "import sqlalchemy\n",
    "import pandas as pd\n",
    "import datetime\n",
    "import random"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#创建连接\n",
    "from sqlalchemy import create_engine\n",
    "conn = create_engine('mysql+pymysql://root:159357@localhost:3306/recommend2?charset=utf8')\n",
    "current_day = '2020-07-01'\n",
    "last_day = '2020-06-29'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#获取销售信息\n",
    "sql2 = 'select id from salesstaff;'\n",
    "salesstaff = pd.read_sql(sql2,conn)\n",
    "salesstaff_ids = salesstaff['id'].values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#第一次分配\n",
    "#获取未沟通过的用户\n",
    "sql1 = 'select id as customer_id,tel from customer where id not in (select customer_id from mission group by customer_id);'\n",
    "customer = pd.read_sql(sql1,conn)\n",
    "# customer.to_dict()\n",
    "\n",
    "def get_mission_data1():\n",
    "    '''\n",
    "    [\n",
    "        {\"salesstaff_id\":id,\"customer_id\":id,\"tel\":tel}\n",
    "    ]\n",
    "    '''\n",
    "    datas = []\n",
    "    customer_dict = customer.to_dict()\n",
    "    \n",
    "    for index,salesstaff_id in enumerate(salesstaff_ids):\n",
    "        for i in range(index * 25,(index+1) * 25 ):\n",
    "            customer_id = customer_dict['customer_id'][i]\n",
    "            customer_tel = customer_dict['tel'][i]\n",
    "            \n",
    "            datas.append(\n",
    "                {\n",
    "                    'salesstaf_id':salesstaff_id,\n",
    "                    'customer_id':customer_id,\n",
    "                    'tel':customer_tel,\n",
    "                })\n",
    "            \n",
    "    return datas\n",
    "    \n",
    "datas1 = get_mission_data1()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#第二次分配\n",
    "sql3 = 'select mission.customer_id as customer_id,c.tel as tel,mission.salesstaff_id as salesstaff_id from mission inner join customer c on mission.customer_id = c.id where customer_id in ( select customer_id from mission where createDate = \"2020-06-29\" and status_id = 1);'\n",
    "last_mission = pd.read_sql(sql3,conn)\n",
    "last_mission.head()\n",
    "def get_mission_data2():\n",
    "    datas = []\n",
    "    cache_salesstaff_ids = set(salesstaff_ids)\n",
    "    for customer_id,groupby_customerid in last_mission.groupby('customer_id'):\n",
    "        # 手机号\n",
    "        tel = groupby_customerid['tel'].iloc[0]\n",
    "        \n",
    "        cache2_salesstaff_ids = list(cache_salesstaff_ids - set(groupby_customerid['salesstaff_id'].values.tolist()))\n",
    "        if not cache2_salesstaff_ids:\n",
    "            cache_salesstaff_ids = set(salesstaff_ids)\n",
    "            cache2_salesstaff_ids = list(cache_salesstaff_ids - set(groupby_customerid['salesstaff_id'].values.tolist()))\n",
    "        \n",
    "        # 销售id    \n",
    "        choice_salesstaff_id = random.choice(cache2_salesstaff_ids)\n",
    "        cache_salesstaff_ids = cache_salesstaff_ids - set([choice_salesstaff_id,])\n",
    "        datas.append(\n",
    "                {\n",
    "                    'salesstaf_id':choice_salesstaff_id,\n",
    "                    'customer_id':customer_id,\n",
    "                    'tel':tel,\n",
    "                }\n",
    "        )\n",
    "    return datas\n",
    "datas2 = get_mission_data2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>salesstaf_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>tel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>9237</td>\n",
       "      <td>cba127ff7cd2d488a40f64e0ec5976ba</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>9238</td>\n",
       "      <td>67a213c027d4f9c518a6b25835a45176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>9239</td>\n",
       "      <td>8c1addec4a69b7711de4950c6dfdb8cd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>9240</td>\n",
       "      <td>ccfb5a6031b1c9c989d57261ffaab5eb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>9241</td>\n",
       "      <td>92528f53b773be9c1a138ac5e0b5b78e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1059</th>\n",
       "      <td>2</td>\n",
       "      <td>29887</td>\n",
       "      <td>cde8850fc2a26e6ba63dde618d7afc17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1060</th>\n",
       "      <td>23</td>\n",
       "      <td>29889</td>\n",
       "      <td>8fe152dc38c99250600ac50873fc0298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1061</th>\n",
       "      <td>4</td>\n",
       "      <td>29892</td>\n",
       "      <td>478d1ac970fbdbeaa1eb57d419c35e72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1062</th>\n",
       "      <td>9</td>\n",
       "      <td>29896</td>\n",
       "      <td>4f70d113b19f59016c87ecdcb8c2f9d2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1063</th>\n",
       "      <td>5</td>\n",
       "      <td>29897</td>\n",
       "      <td>4550dcb5340befa3344c05bf67bc627f</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1064 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      salesstaf_id  customer_id                               tel\n",
       "0                2         9237  cba127ff7cd2d488a40f64e0ec5976ba\n",
       "1                2         9238  67a213c027d4f9c518a6b25835a45176\n",
       "2                2         9239  8c1addec4a69b7711de4950c6dfdb8cd\n",
       "3                2         9240  ccfb5a6031b1c9c989d57261ffaab5eb\n",
       "4                2         9241  92528f53b773be9c1a138ac5e0b5b78e\n",
       "...            ...          ...                               ...\n",
       "1059             2        29887  cde8850fc2a26e6ba63dde618d7afc17\n",
       "1060            23        29889  8fe152dc38c99250600ac50873fc0298\n",
       "1061             4        29892  478d1ac970fbdbeaa1eb57d419c35e72\n",
       "1062             9        29896  4f70d113b19f59016c87ecdcb8c2f9d2\n",
       "1063             5        29897  4550dcb5340befa3344c05bf67bc627f\n",
       "\n",
       "[1064 rows x 3 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#合并并保存数据\n",
    "datas_total = datas1 + datas2\n",
    "new_mission = pd.DataFrame(datas_total)\n",
    "#存入到新的任务表\n",
    "#new_mission.to_sql('2020-07-01-mission',conn,index=False)\n",
    "#new_mission.to_excel('2020-07-01-mission.xlsx',index=False)\n",
    "new_mission"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#更新任务表\n",
    "new_mission = new_mission.drop('tel',axis=1)\n",
    "new_mission['createDate'] = current_day\n",
    "new_mission['status_id'] = 7\n",
    "new_mission.columns = ['salesstaff_id', 'customer_id', 'createDate', 'status_id']\n",
    "new_mission.to_sql('mission',conn,index=False,if_exists='append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
